package com.matt.repository.impl;

import com.matt.foundation.query.AdviseQuery;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.GardenAdviseChildrenRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/9/19
 * *******************************
 */
@Component
public class GardenAdviseChildrenRepositoryImpl extends BaseNativeSqlRepository implements GardenAdviseChildrenRepository {

    @Override
    public List<Object[]> findChildrenByAreaId(AdviseQuery query) {
        String querySql = String.format("SELECT c.id, c.name, c.gender, c.birthday, g.content " +
                " from children c left JOIN garden_leader_advise g ON c.id = g.childrenId where true ");

        if (StringUtils.isNotBlank(query.getAreaId())) {
            querySql += String.format(" and c.aid='%s' ", query.getAreaId());
        }
        if (StringUtils.isNotBlank(query.getChildrenName())) {
            querySql += String.format(" and c.name like concat('%s', '%s', '%s') ", "%", query.getChildrenName(), "%");
        }

        querySql += String.format(" and c.isDisplayForAdmin = false ");
        querySql += " order by c.createTime desc " ;
        querySql += String.format(" limit %s, %s ", query.getPageNum() * query.getPageSize() ,  query.getPageSize());
        List<Object[]> objectArrayList = sqlArrayList(querySql);
        return objectArrayList;
    }

    @Override
    public Long countChildrenByAreaId(AdviseQuery query) {
        String querySql = String.format("SELECT count(c.id) " +
                " from children c left JOIN garden_leader_advise g ON c.id = g.childrenId where true ");

        if (StringUtils.isNotBlank(query.getAreaId())) {
            querySql += String.format(" and c.aid='%s' ", query.getAreaId());
        }
        if (StringUtils.isNotBlank(query.getChildrenName())) {
            querySql += String.format(" and c.name like concat('%s', '%s', '%s') ", "%", query.getChildrenName(), "%");
        }
        querySql += String.format(" and c.isDisplayForAdmin = false ");
        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }


    @Override
    public List<Object[]> findChildrenByClasses(String classesId) {
        String querySql = String.format("SELECT c.name, g.content " +
                " from garden_leader_advise g LEFT JOIN children c  ON c.id = g.childrenId " +
                " left join rel_classes_children rel on rel.childrenId = c.id where rel.classesId = '%s' ", classesId);
        List<Object[]> objectArrayList = sqlArrayList(querySql);
        return objectArrayList;
    }
}
